﻿ --exec sp_planning_report_detail_by_backLog 0, 0,'ALL'		
 ALTER PROC sp_planning_report_detail_by_backLog
 
 @BASEPRODUCT_ID INT,
 @PS_STEP_ID INT,
 @CHIP_SET_VALUE   VARCHAR(20)
 as
 begin
  declare @RESULTS_TABLE table
   ( [Customer Name] varchar(100), [Order No] VARCHAR(50), [Line No] VARCHAR(50), 
     [Request Date] DATETIME, [Mkt Part Number] varchar(50), [Mfg Part Number] varchar(50), [Remaining Qty] INT,[CSD/Revised Date] DATETIME,
     [FG] int,[Tape & Reel] int,[FT] int,[Assembly] int,[Die Bank] int,[Fab Option] int,[Fab] int
    
   )
  
  declare @baseproduct varchar(50)
  declare @mktpartnumber varchar(50)
  declare @fabpartnumber  varchar(50)
  declare @fgprocessstep_id int
  declare @product_id int
  declare @processstep_id int
  declare @ps_type varchar(50)
  declare @fab_qty int
  declare @wafer_bank_qty int
  declare @Fab_Option_qty int
  declare @diebank_qty int
  declare @assembly_qty int
  declare @pre_test_qty int
  declare @ft_qty  int
  declare @tapeReel_qty int
  declare @fg_qty  int
  declare @ship_qty int
  DECLARE @CHIPSET    VARCHAR(20)
  DECLARE @PART_NUMBER VARCHAR(50)
  declare @lot_cursor cursor
  declare @assm_yield float
  declare @test_yield float
  DECLARE @CURRENT_QUARTER INT
 
  SELECT @CURRENT_QUARTER = CQC_QUARTER_NO FROM COMPANY_QUARTERLY_CALENDAR WHERE CONVERT(VARCHAR, CURRENT_TIMESTAMP, 101 ) >= CQC_START_DATE AND CONVERT( VARCHAR, CURRENT_TIMESTAMP, 101) <= CQC_END_DATE
  
  DECLARE @CURSOR_STRING NVARCHAR(1000)
 
   SET @CURSOR_STRING = N'DECLARE product_cursor CURSOR FOR SELECT DISTINCT p_marketing_part_number, p.Processstep_id,
     (select part_number from product where product_id = p.p_parent_product_id ), P.product_id, Part_Number
      FROM Customer_Open_Order_LineItem COOL, SALES_ORDER SO, CUSTOMER C, PRODUCT P WHERE
     P.PRODUCT_ID = COOL.PRODUCT_ID AND COOL.SalesOrder_ID = SO.SalesOrder_ID AND SO.Customer_ID = C.Customer_ID AND COOL_STATUS <> ''SHIPPED'' '
  
  IF @BASEPRODUCT_ID <> '0' 
  BEGIN
   SET @CURSOR_STRING = @CURSOR_STRING + ' AND P.P_Parent_Product_ID = '+ CONVERT(VARCHAR, @BASEPRODUCT_ID)
  END
  
  IF @PS_STEP_ID <> '0' 
  BEGIN
   SET @CURSOR_STRING = @CURSOR_STRING + ' AND P.ProcessStep_ID = '+ CONVERT(VARCHAR, @PS_STEP_ID)
  END
  
  PRINT @CURSOR_STRING
 
  EXECUTE SP_EXECUTESQL @CURSOR_STRING
  
  OPEN product_cursor
  FETCH NEXT FROM product_cursor INTO @mktpartnumber, @fgprocessstep_id, @baseproduct, @product_id, @PART_NUMBER
  WHILE @@FETCH_STATUS = 0
  BEGIN
   SELECT @CHIPSET = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'ChipSET1' AND PROCESSSTEP_ID = @fgprocessstep_id    
   set @lot_cursor = cursor for
   select distinct processstep_id,ps_type from FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID (@fgprocessstep_id,0)
   OPEN @lot_cursor
   FETCH NEXT FROM @lot_cursor INTO @processstep_id,@ps_type
   WHILE @@FETCH_STATUS = 0
   BEGIN
    if( @ps_type = 'fab')
    begin
     set @ship_qty = null
     select @fab_qty = sum(wafer_qty_in) from lot_Table where processstep_id = @processstep_id and lot_current_status <> 'close'
     select @ship_qty = SUM(ship_qty) from shipped_lot sl,lot_table lt where sl.lot_id = lt.lot_id and lt.processstep_id = @processstep_id and status != 'Close'
     set @fab_qty = isnull(@fab_qty,0) + isnull(@ship_qty,0)
    end
    else if( @ps_type = 'Wafer Bank')
    begin
     set @ship_qty = null
     select @wafer_bank_qty = sum(wafer_qty_in) from lot_Table where processstep_id = @processstep_id and lot_current_status <> 'close'
 
     select @ship_qty = SUM(ship_qty) from shipped_lot sl,lot_table lt where sl.lot_id = lt.lot_id and lt.processstep_id = @processstep_id and status != 'Close'
 
     set @wafer_bank_qty = isnull(@wafer_bank_qty,0) + isnull(@ship_qty,0)
    end
    else if( @ps_type = 'Fab Option')
    begin
     set @ship_qty = null
     select @Fab_Option_qty = sum(wafer_qty_in) from lot_Table where processstep_id = @processstep_id and lot_current_status <> 'close'
 
     select @ship_qty = SUM(ship_qty) from shipped_lot sl,lot_table lt where sl.lot_id = lt.lot_id and lt.processstep_id = @processstep_id and status != 'Close'
 
     set @Fab_Option_qty = isnull(@Fab_Option_qty,0) + isnull(@ship_qty,0)
    end
    else if( @ps_type = 'die bank')
    begin
     set @ship_qty = null
     select @diebank_qty = sum(Qty_in) from lot_Table where processstep_id = @processstep_id and lot_current_status <> 'close'
 
     select @ship_qty = SUM(ship_qty) from shipped_lot sl,lot_table lt where sl.lot_id = lt.lot_id and lt.processstep_id = @processstep_id and status != 'Close'
 
     set @diebank_qty = isnull(@diebank_qty,0) + isnull(@ship_qty,0)
    end
    else if( @ps_type = 'assembly')
    begin
     set @ship_qty = null
     select @assembly_qty = sum(Qty_in) from lot_Table where processstep_id = @processstep_id and lot_current_status <> 'close'
     select @ship_qty = SUM(ship_qty) from shipped_lot sl,lot_table lt where sl.lot_id = lt.lot_id and lt.processstep_id = @processstep_id and status != 'Close'
     set @assembly_qty = isnull(@assembly_qty,0) + isnull(@ship_qty,0)
     SELECT @assm_yield = (CASE WHEN @CURRENT_QUARTER = 1 THEN Q1_yield WHEN @CURRENT_QUARTER = 2 then Q2_Yield WHEN @CURRENT_QUARTER = 3 then Q3_Yield ELSE Q4_Yield END) FROM Update_View_Cost_Table where ProcessStep_Id = @processstep_id AND Year(Fin_Year) = Year(CURRENT_TIMESTAMP)
     --SELECT @test_yield = (CASE WHEN @CURRENT_QUARTER = 1 THEN Q1_yield WHEN @CURRENT_QUARTER = 2 then Q2_Yield WHEN @CURRENT_QUARTER = 3 then Q3_Yield ELSE Q4_Yield END) FROM Update_View_Cost_Table where ProcessStep_Id = ( Select Processstep_Id from FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID( @processstep_id, 1) where PS_Type = 'FT' ) AND Year(Fin_Year) = Year(CURRENT_TIMESTAMP)
     IF @assm_yield IS NULL OR @assm_yield <= 0
     BEGIN
      SET @assm_yield = 100
     END
 
     IF @test_yield IS NULL OR @test_yield <= 0
     BEGIN
      SET @test_yield = 100
     END
     SET @assembly_qty = (@assembly_qty*(@assm_yield/100))*(@test_yield/100)
 
    end
    else if( @ps_type = 'Pre Test')
    begin
     set @ship_qty = null
     select @pre_test_qty = sum(Qty_in) from lot_Table where processstep_id = @processstep_id and lot_current_status <> 'close'
     select @ship_qty = SUM(ship_qty) from shipped_lot sl,lot_table lt where sl.lot_id = lt.lot_id and lt.processstep_id = @processstep_id and status != 'Close'
 
     set @pre_test_qty = isnull(@pre_test_qty,0) + isnull(@ship_qty,0)
     SELECT @test_yield = (CASE WHEN @CURRENT_QUARTER = 1 THEN Q1_yield WHEN @CURRENT_QUARTER = 2 then Q2_Yield WHEN @CURRENT_QUARTER = 3 then Q3_Yield ELSE Q4_Yield END) FROM Update_View_Cost_Table where ProcessStep_Id = ( Select Processstep_Id from FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID( @processstep_id, 1) where PS_Type = 'FT' ) AND Year(Fin_Year) = Year(CURRENT_TIMESTAMP)
 
     IF @test_yield IS NULL OR @test_yield <= 0
     BEGIN
      SET @test_yield = 100
     END
     SET @pre_test_qty = (ISNULL(@pre_test_qty,0)*(@test_yield/100))
 
    end
    else if( @ps_type = 'ft')
    begin
     set @ship_qty = null
     select @ft_qty = sum(Qty_in) from lot_Table where processstep_id = @processstep_id and lot_current_status <> 'close'
 
     select @ship_qty = SUM(ship_qty) from shipped_lot sl,lot_table lt where sl.lot_id = lt.lot_id and lt.processstep_id = @processstep_id and status != 'Close'
 
     set @ft_qty = isnull(@ft_qty,0) + isnull(@ship_qty,0)
	 			
     SELECT @test_yield = (CASE WHEN @CURRENT_QUARTER = 1 THEN Q1_yield WHEN @CURRENT_QUARTER = 2 then Q2_Yield WHEN @CURRENT_QUARTER = 3 then Q3_Yield ELSE Q4_Yield END) FROM Update_View_Cost_Table where ProcessStep_Id = @processstep_id AND Year(Fin_Year) = Year(CURRENT_TIMESTAMP)
 
     IF @test_yield IS NULL OR @test_yield <= 0
     BEGIN
      SET @test_yield = 100
     END
	 
     SET @ft_qty = (@ft_qty*(@test_yield/100))
	 
    end
    else if( @ps_type = 'Tape & Reel')
    begin
     set @ship_qty = null
     select @tapeReel_qty = sum(Qty_in) from lot_Table where processstep_id = @processstep_id and lot_current_status <> 'close'
 
     select @ship_qty = SUM(ship_qty) from shipped_lot sl,lot_table lt where sl.lot_id = lt.lot_id and lt.processstep_id = @processstep_id and status != 'Close'
 
     set @tapeReel_qty = isnull(@tapeReel_qty,0) + isnull(@ship_qty,0)
    end
    else if( @ps_type = 'fg' and @fgprocessstep_id = @processstep_id)
    begin
     set @ship_qty = null
     select @fg_qty = sum(Qty_in) from lot_Table where processstep_id = @processstep_id and lot_current_status <> 'close'
     select @ship_qty = SUM(ship_qty) from shipped_lot sl,lot_table lt where sl.lot_id = lt.lot_id and lt.processstep_id = @processstep_id and status != 'Close'
     set @fg_qty = isnull(@fg_qty,0) + isnull(@ship_qty,0)
    end
   FETCH NEXT FROM @lot_cursor INTO @processstep_id,@ps_type
   END
   CLOSE @lot_cursor
   DEALLOCATE @lot_cursor
     
   INSERT INTO @RESULTS_TABLE ([Mkt Part Number],[Mfg Part Number],[FG],[Tape & Reel],[FT],[Assembly],[Die Bank],[Fab Option],[Fab])
   VALUES
   (@mktpartnumber,@PART_NUMBER,@fg_qty,@tapeReel_qty,(ISNULL(@ft_qty,0) +ISNULL(@pre_test_qty,0)),ISNULL(@assembly_qty,0),@diebank_qty, (ISNULL(@Fab_Option_qty,0)+ isNULL(@wafer_bank_qty,0)) ,@fab_qty)
 
   INSERT INTO @RESULTS_TABLE( [Customer Name],[Order No],[Line No],[Request Date],
   [Mkt Part Number],[Mfg Part Number],[Remaining Qty],[CSD/Revised Date] )
   SELECT C_NAME,SalesOrder_No,CONVERT(VARCHAR,COOL_Line_Item_No),COOL_requested_Date,@mktpartnumber,@PART_NUMBER,COOL_Qty,ISNULL(COOL_revised_Date,COOL_SCHEDULE_DATE) 
     FROM Customer_Open_Order_LineItem COOL, SALES_ORDER SO, CUSTOMER C, PRODUCT P WHERE
   P.PRODUCT_ID = COOL.PRODUCT_ID AND COOL.SalesOrder_ID = SO.SalesOrder_ID AND SO.Customer_ID = C.Customer_ID AND P.PRODUCT_ID = @product_id AND COOL_STATUS <> 'Shipped'
   
   
   SET @CHIPSET  = null
   SET @PART_NUMBER    = null
   set @baseproduct = null
   set @mktpartnumber = null
   set @fgprocessstep_id  = null
   set @processstep_id = null
   SET @product_id  = null
   set @ps_type  = null
   set @fab_qty  = null
   set @wafer_bank_qty = null
   set @Fab_Option_qty = null
   set @diebank_qty = null
   set @assembly_qty = null
   set @pre_test_qty = null
   set @ft_qty  = null
   set @tapeReel_qty  = null
   set @fg_qty  = null
      
  FETCH NEXT FROM product_cursor INTO @mktpartnumber,@fgprocessstep_id,@baseproduct,@product_id,@PART_NUMBER
  END
  CLOSE product_cursor
  DEALLOCATE product_cursor
 
  DECLARE @Result_Cursor CURSOR
  DECLARE @Ship_Req_No VARCHAR(50)
  DECLARE @Order_No VARCHAR(50)
  DECLARE @Line_No VARCHAR(50)
  DECLARE @Order_Qty INT
  DECLARE @FG_Qty_Map INT
  DECLARE @tapeReel_qty_Map INT
  DECLARE @FT_Qty_Map INT
  DECLARE @Assm_Qty_Map INT
   
  SET @Result_Cursor = CURSOR FOR SELECT [Order No], [Line No], [Mfg Part Number], [Remaining Qty],
     [FG],[Tape & Reel],[FT],[Assembly] FROM @RESULTS_TABLE ORDER BY [Mfg Part Number],[CSD/Revised Date],[Order No],[Line No]
 
  OPEN @Result_Cursor
  FETCH NEXT FROM @Result_Cursor INTO @Order_No, @Line_No, @PART_NUMBER, @Order_Qty, @fg_qty, @tapeReel_qty, @ft_qty, @assembly_qty
  WHILE @@FETCH_STATUS = 0
  BEGIN
   IF @Order_No IS NULL AND @Line_No IS NULL
   BEGIN
    SET @FG_Qty_Map = ISNULL(@fg_qty, 0)
    SET @tapeReel_qty_Map = @FG_Qty_Map + ISNULL(@tapeReel_qty,0)
    SET @FT_Qty_Map = @tapeReel_qty_Map + ISNULL( @ft_qty,0)
    SET @Assm_Qty_Map = @FT_Qty_Map + ISNULL( @assembly_qty,0)
   END
   ELSE
   BEGIN
    SET @FG_Qty_Map = @FG_Qty_Map - @Order_Qty
    SET @tapeReel_qty_Map = @tapeReel_qty_Map - @Order_Qty
    SET @FT_Qty_Map = @FT_Qty_Map - @Order_Qty
    SET @Assm_Qty_Map = @Assm_Qty_Map - @Order_Qty
    UPDATE @RESULTS_TABLE SET [FG] = @FG_Qty_Map, [Tape & Reel] = @tapeReel_qty_Map, [FT] = @FT_Qty_Map,[Assembly] = @Assm_Qty_Map WHERE [Order No] = @Order_No AND [Line No] = @Line_No
   END
   FETCH NEXT FROM @Result_Cursor INTO @Order_No, @Line_No, @PART_NUMBER, @Order_Qty, @fg_qty, @tapeReel_qty, @ft_qty, @assembly_qty
  END
  CLOSE @Result_Cursor
  DEALLOCATE @Result_Cursor
 
  select * from  @RESULTS_TABLE order by [Mfg Part Number],[CSD/Revised Date],[Order No],[Line No]--[Request Date]
 
  
 END
